<?php

namespace App\Model\Foundation;


use App\Model\Business\Schedule;
use App\Model\Business\ScheduleStuden;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Statistic extends Model
{
    public $table = '';
    public $primaryKey = '';


    /**
     * 获取教师课表统计
     * @param $begintime
     * @param $endtime
     * @return array
     */
    static function _teacherSchedules($begintime, $endtime)
    {
        $teachers = [];

        $query = DB::table('schedules')
                    ->select(DB::raw('count(*) as counts, teacheruid, coursetypeid'))
                    ->groupBy('teacheruid')
                    ->groupBy('coursetypeid');
        if($begintime){
            $query->where('begintime', '>=', $begintime);
        }
        if($endtime){
            $query->where('endtime', '<', $endtime);
        }
        $results = $query->get();

        $infos=[];
        foreach ($results as $rst){
            $tid = $rst->teacheruid;
            if(!key_exists($tid, $infos)){
                $teacher = Teacher::find($tid);
                $infos[$tid] = [];
                $infos[$tid]['teacheruid'] = $tid;
                $infos[$tid]['teacher'] = $teacher->lastname;
                $infos[$tid]['coursetypes'] = [];
            }
            $ctid = $rst->coursetypeid;
            $infos[$tid]['coursetypes'][$ctid] = $rst->counts;
        }

        return $infos;
    }

    /**
     * 按照真实课时时间计算课时
     * @param $begintime
     * @param $endtime
     * @return array
     */
    static function teacherSchedules($begintime, $endtime)
    {
        # 查询
        $query = Schedule::where('status',1);
        if($begintime){
            $query->where('begintime', '>=', $begintime);
        }
        if($endtime){
            $query->where('endtime', '<', $endtime);
        }
        $results = $query->get();
        # 附加学生
        foreach ($results as $k=>$item){
            $scheduleStudent = ScheduleStuden::getStudentByScheduleID($item->scheduleid);
            $students = [];
            foreach ($scheduleStudent as $student){
                if($student->identity==1){
                    $students[] = $student;
                }
            }
            $results[$k]->students = $students ;
        }

        # 计算
        $infos=[];
        foreach ($results as $rst){
            $tid = $rst->teacheruid;
            if(!key_exists($tid, $infos)){
                $teacher = Teacher::find($tid);
                $infos[$tid] = [];
                $infos[$tid]['teacheruid'] = $tid;
                $infos[$tid]['teacher'] = $teacher->lastname;
                $infos[$tid]['coursetypes'] = [];
            }
            $ctid = $rst->coursetypeid;
            if(key_exists($ctid, $infos[$tid]['coursetypes'])){
                $infos[$tid]['coursetypes'][$ctid] += $rst->realSchedules();
            }else{
                $infos[$tid]['coursetypes'][$ctid] = $rst->realSchedules();
            }
        }

        #返回
        return $infos;
    }


    /**
     * 获取学生的课时数，按次数统计
     * @param $begintime
     * @param $endtime
     * @return array
     */
    static function _studentSchedules($begintime, $endtime)
    {
        $results   =  DB::table('schedules_students as relation')
            ->select(DB::raw('relation.studentuid, students.name, schedules.coursetypeid, count(*) as counts'))
            ->join('students', 'students.studentuid', '=', 'relation.studentuid')
            ->join('schedules',  function ($join) use ($begintime, $endtime) {
                $join->on('relation.scheduleid', '=', 'schedules.scheduleid');
                if($begintime){
                    $join->where('schedules.begintime', '>=', $begintime);
                }
                if($endtime){
                    $join->where('schedules.endtime', '<',  $endtime);
                }
            })
            ->groupby('relation.studentuid')
            ->groupby('schedules.coursetypeid')
            ->get();


        $infos=[];
        foreach ($results as $rst){
            $tid = $rst->studentuid;
            if(!key_exists($tid, $infos)){
                $infos[$tid] = [];
                $infos[$tid]['studentuid'] = $tid;
                $infos[$tid]['name'] = $rst->name;
                $infos[$tid]['coursetypes'] = [];
            }
            $ctid = $rst->coursetypeid;
            $infos[$tid]['coursetypes'][$ctid] = $rst->counts;
        }
        return $infos;
    }

    /**
     * 获取学生的课时数，按照开始时间真实计算
     * @param $begintime
     * @param $endtime
     * @return array
     */
    static function studentSchedules($begintime, $endtime)
    {
        # 查询
        $results   =  DB::table('schedules_students as relation')
            ->select(DB::raw('relation.studentuid, students.name, schedules.coursetypeid, schedules.begintime, schedules.endtime'))
            ->join('students', 'students.studentuid', '=', 'relation.studentuid')
            ->join('schedules',  function ($join) use ($begintime, $endtime) {
                $join->on('relation.scheduleid', '=', 'schedules.scheduleid');
                if($begintime){
                    $join->where('schedules.begintime', '>=', $begintime);
                }
                if($endtime){
                    $join->where('schedules.endtime', '<',  $endtime);
                }
            })
            ->get();

        # 计算
        $infos=[];
        foreach ($results as $rst){
            $tid = $rst->studentuid;
            if(!key_exists($tid, $infos)){
                $infos[$tid] = [];
                $infos[$tid]['studentuid'] = $tid;
                $infos[$tid]['name'] = $rst->name;
                $infos[$tid]['coursetypes'] = [];
            }
            $ctid = $rst->coursetypeid;
            if(key_exists($ctid, $infos[$tid]['coursetypes'])){
                $infos[$tid]['coursetypes'][$ctid] += Schedule::calculRealSchedules($rst->begintime, $rst->endtime);
            }else{
                $infos[$tid]['coursetypes'][$ctid] = Schedule::calculRealSchedules($rst->begintime, $rst->endtime);
            }
        }
        return $infos;
    }


}
